package com.ibm.cognos.imt.controller;

import com.ibm.cognos.imt.config.BiServerConfig;
import com.ibm.cognos.imt.fm.ExpressionRunner;
import com.ibm.cognos.imt.fm.XMLReportParser;
import com.ibm.cognos.imt.service.CognosReportService;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.xml.rpc.ServiceException;
import java.io.*;
import java.net.MalformedURLException;
import java.util.Iterator;

/**
 * Created by eric on 8/31/2014.
 */
@Controller
public class ExcelController {

    @Autowired
    CognosReportService reportService;

    @Autowired
    BiServerConfig serverConfig;

    @RequestMapping(value="/tweakExcel", method= RequestMethod.PUT)
    public @ResponseBody String convertExcel() throws IOException {
        File uploadedFile = new File("uploaded.xls");
        System.out.println(uploadedFile.getCanonicalPath());
        InputStream inputStream = new FileInputStream(uploadedFile);
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(inputStream));
        HSSFSheet sheet = wb.getSheetAt(0);
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) {
//                    case Cell.CELL_TYPE_NUMERIC:
//                        System.out.print(cell.getNumericCellValue() + "t");
//                        break;
                    case Cell.CELL_TYPE_STRING:
                        String expression = cell.getStringCellValue();
                        String value = runExpressionSpec(expression);
                        if (!value.equals(expression)) {
                            System.out.println("turn "+expression+ " into "+value);
                            cell.setCellValue(value);
                        }
//                      System.out.print(cell.getStringCellValue() + "t");
                        break;
                }
            }
        }
        inputStream.close();

        FileOutputStream outFile =new FileOutputStream(new File("tweaked.xls"));
        wb.write(outFile);
        outFile.flush();
        outFile.close();
        return "OK";
    }

    private String runExpressionSpec(String value) {
        ExpressionRunner expressionRunner = new ExpressionRunner(serverConfig);
        String spec = expressionRunner.generateSpec(value);
        if (spec.equals(value)) {
            return value;
        } else {
            String[] result = new String[0];
            try {
                result = reportService.runSpec(spec, "XML");
            } catch (MalformedURLException e) {
                e.printStackTrace();
            } catch (ServiceException e) {
                e.printStackTrace();
            }
//            System.out.println(result[0]);
            return new XMLReportParser().getListResult(result[0]);
        }
    }
}
